﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading;
using BoYuanCore.Framework;
using FreeSql;
using FreeSql.Aop;
using FreeSql.DataAnnotations;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;

//官方文档 freesql.net

namespace BoYuanCore.DBServices.Base
{
    public static class FreeSqlHelper   
    {
        private static IFreeSql _instance = null;
        private static readonly object _instanceLock = new object();

        //由于freesql采用单例模式，故改配置时候不会热更。需要重启iis等服务器
        private  static string _ConnectionString => Appsettings.app(new string[] { "AppSettings", "DbConnection", "ConnectionString" });

        /// <summary>          
        /// 获取实例(单例模式)        
        /// </summary>          
        /// <returns></returns>          
        public static IFreeSql GetInstance()
        {
            if (_instance == null)
                lock (_instanceLock)
                    if (_instance == null)
                    {
                        _instance = new FreeSql.FreeSqlBuilder()
                              .UseConnectionString(FreeSql.DataType.SqlServer, _ConnectionString)
                              //.UseGenerateCommandParameterWithLambda(true)//lambda 是否启用参数化
                              .UseAutoSyncStructure(false) //自动同步实体结构到数据库
                              //.UseMonitorCommand(cmd => Console.WriteLine($"线程：{cmd.CommandText}\r\n"))//监听数据库sql(不想在这里做处理)
                              .Build(); //请务必定义成 Singleton 单例模式
                    }
            return _instance;
        }
        // FreeSqlAop功能在mvc项目里FreeSqlSetup方法来实现，本框架层不想注入log组件 ！！！！

        #region 获取实体 https://github.com/dotnetcore/FreeSql/wiki/%E6%9F%A5%E8%AF%A2

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">主键</param>
        /// <returns></returns>
        public static T GetModel<T>(object id) where T : class
        {
            return GetInstance().Select<T>().WhereDynamic(id).First();
        }

        #endregion

        #region 添加 https://github.com/dotnetcore/FreeSql/wiki/%E6%B7%BB%E5%8A%A0
        //全部列 < 指定列(InsertColumns) < 忽略列(IgnoreColumns)

        /// <summary>
        /// 添加实体数据，并返回主键值(主键为long类型雪花id,实体需要设置主键特性),null值也会插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static long InsertModel<T>(T t) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            int tempNum = GetInstance().Insert<T>(t).ExecuteAffrows();
            return tempNum == 0 ? 0 : t.ID;//0为插入失败
        }

        /// <summary>
        /// 添加实体数据,并指定插入字段集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="insertColumns"></param>
        /// <returns></returns>
        public static long InsertModel<T>(T t, Expression<Func<T, object>> insertColumns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            int tempNum = GetInstance().Insert<T>(t).InsertColumns(insertColumns).ExecuteAffrows();
            return tempNum == 0 ? 0 : t.ID;//0为插入失败
        }

        /// <summary>
        /// 插入一条，并忽略指定字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="ignoreColumns">要忽略的字段</param>
        /// <returns></returns>
        public static long InsertModel_Ignore<T>(T t, Expression<Func<T, object>> ignoreColumns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            int tempNum = GetInstance().Insert<T>(t).IgnoreColumns(ignoreColumns).ExecuteAffrows();
            return tempNum == 0 ? 0 : t.ID;//0为插入失败
        }

        #endregion

        #region 更新 https://github.com/dotnetcore/FreeSql/wiki/%E4%BF%AE%E6%94%B9
        //> 全部列 < 指定列(Set/SetRaw) < 忽略列(IgnoreColumns) 

        /// <summary>
        /// 根据主键更新实体，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int UpdateModels<T>(T t) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            return GetInstance().Update<T>().SetSource(t).ExecuteAffrows();
        }

        /// <summary>
        /// 根据主键更新实体(要指定更新的列)，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static int UpdateModels<T>(T t, Expression<Func<T, object>> columns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            return GetInstance().Update<T>().SetSource(t).UpdateColumns(columns).ExecuteAffrows();
            /*
             mo=new entity(){FontIcon=1,Icon=2,ID=1};
             FreeSqlHelper.UpdateModels(mo,p=>new {p.FontIcon,p.Icon })
             */
        }

        /// <summary>
        /// 根据主键更新实体(并忽略指定字段)，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="ignoreColumns">要忽略的字段</param>
        /// <returns></returns>
        public static int UpdateModels_Ignore<T>(T t, Expression<Func<T, object>> ignoreColumns) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            return GetInstance().Update<T>().SetSource(t).IgnoreColumns(ignoreColumns).ExecuteAffrows();
        }


        /// <summary>
        /// 动态更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns">要更新的字段</param>
        /// <param name="expressionWhere">条件表达式</param>
        /// <returns></returns>
        public static int Update<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> expressionWhere) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            //这个方法也可以实现：GetInstance().Update<T>(expressionWhere).Set(columns).ExecuteAffrows();
            return GetInstance().Update<T>().Set(columns).Where(expressionWhere).ExecuteAffrows();
            // FreeSqlHelper.Update<SysModule>(p => new SysModule() {Icon = src}, p => p.ID.ToString() == id);
        }

        /*  update子查询。 注意freesql的Update方法只能单表操作。
                UPDATE sysModule
                    SET	
                         isend=0
                WHERE id=(SELECT parentID FROM sysModule AS sm WHERE sm.id=0)
            
            //多表update操作或update子查询，应该如下写法。
            int tempNum = db.Select<Entities.SysModule>()
                .Where(p => db.Select<Entities.SysModule>().Where(m => m.ID == id && p.ID == m.ParentID).Any())
                .ToUpdate()
                .Set(p => new Entities.SysModule() {IsLeaf = false})
                .ExecuteAffrows();
         */

        #endregion

        #region 删除方法 https://github.com/dotnetcore/FreeSql/wiki/%E5%88%A0%E9%99%A4

        /// <summary>
        /// 删除ids集合条件的字符串
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式</param>
        /// <returns></returns>
        public static bool DeleteByIds<T>( string ids) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            var idList = GetLongListByString(ids);
            return GetInstance().Delete<T>(idList).ExecuteAffrows() > 0;
        }

        /// <summary>
        /// 根据条件表达式删除，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">表达式</param>
        /// <returns></returns>
        public static int DeleteModels<T>( Expression<Func<T, bool>> expression) where T : BoYuanCore.Entities.SnowflakEntity, new()
        {
            //此方法也可以实现 ：GetInstance().Delete<T>(expression).ExecuteAffrows();
            return GetInstance().Delete<T>().Where(expression).ExecuteAffrows();
        }

        #endregion

        #region ids转集合

        /// <summary>
        /// 将字符串转成int数组(, 号分割)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static int[] GetIntListByString(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return null;
            return Array.ConvertAll<string, int>(ids.Split(','), Int32.Parse);
        }

        /// <summary>
        /// 将字符串转成long数组(, 号分割)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static long[] GetLongListByString(string ids)
        {
            if (string.IsNullOrWhiteSpace(ids)) return null;
            return Array.ConvertAll<string, long>(ids.Split(','),Convert.ToInt64);
        }

        #endregion
    }

    [ExpressionCall]
    public static class MyFreeSqlExpressionCall
    {
        //https://github.com/dotnetcore/FreeSql/issues/243 In多列查询，表达式自定义实现 

        public static ThreadLocal<ExpressionCallContext> expContext = new ThreadLocal<ExpressionCallContext>();
        /// <summary>
        /// C#：从元组集合中查找 exp1, exp2 是否存在<para></para>
        /// SQL： <para></para>
        /// exp1 = that[0].Item1 and exp2 = that[0].Item2 OR <para></para>
        /// exp1 = that[1].Item1 and exp2 = that[1].Item2 OR <para></para>
        /// ... <para></para>
        /// 注意：当 that 为 null 或 empty 时，返回 1=0
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <param name="that"></param>
        /// <param name="exp1"></param>
        /// <param name="exp2"></param>
        /// <returns></returns>
        public static bool Contains<T1, T2>([RawValue] this IEnumerable<(T1, T2)> that, T1 exp1, T2 exp2)
        {
            if (expContext.IsValueCreated == false || expContext.Value == null || expContext.Value.ParsedContent == null)
                return that?.Any(a => a.Item1.Equals(exp1) && a.Item2.Equals(exp2)) == true;
            if (that?.Any() != true)
            {
                expContext.Value.Result = "1=0";
                return false;
            }
            var sb = new StringBuilder();
            var idx = 0;
            foreach (var item in that)
            {
                if (idx++ > 0) sb.Append(" OR \r\n");
                sb
                    .Append(expContext.Value.ParsedContent["exp1"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T1), item.Item1)))
                    .Append(" AND ")
                    .Append(expContext.Value.ParsedContent["exp2"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T2), item.Item2)));
            }
            expContext.Value.Result = sb.ToString();
            return true;
        }
        /// <summary>
        /// C#：从元组集合中查找 exp1, exp2, exp2 是否存在<para></para>
        /// SQL： <para></para>
        /// exp1 = that[0].Item1 and exp2 = that[0].Item2 and exp3 = that[0].Item3 OR <para></para>
        /// exp1 = that[1].Item1 and exp2 = that[1].Item2 and exp3 = that[1].Item3 OR <para></para>
        /// ... <para></para>
        /// 注意：当 that 为 null 或 empty 时，返回 1=0
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <typeparam name="T3"></typeparam>
        /// <param name="that"></param>
        /// <param name="exp1"></param>
        /// <param name="exp2"></param>
        /// <param name="exp3"></param>
        /// <returns></returns>
        public static bool Contains<T1, T2, T3>([RawValue] this IEnumerable<(T1, T2, T3)> that, T1 exp1, T2 exp2, T3 exp3)
        {
            if (expContext.IsValueCreated == false || expContext.Value == null || expContext.Value.ParsedContent == null)
                return that.Any(a => a.Item1.Equals(exp1) && a.Item2.Equals(exp2) && a.Item3.Equals(exp3));
            if (that.Any() == false)
            {
                expContext.Value.Result = "1=0";
                return false;
            }
            var sb = new StringBuilder();
            var idx = 0;
            foreach (var item in that)
            {
                if (idx++ > 0) sb.Append(" OR \r\n");
                sb
                    .Append(expContext.Value.ParsedContent["exp1"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T1), item.Item1)))
                    .Append(" AND ")
                    .Append(expContext.Value.ParsedContent["exp2"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T2), item.Item2)))
                    .Append(" AND ")
                    .Append(expContext.Value.ParsedContent["exp3"]).Append(" = ").Append(expContext.Value.FormatSql(FreeSql.Internal.Utils.GetDataReaderValue(typeof(T3), item.Item3)));
            }
            expContext.Value.Result = sb.ToString();
            return true;
        }

        /*
//元组集合
vae lst = new List<(Guid, DateTime)>();
lst.Add((Guid.NewGuid(), DateTime.Now));
lst.Add((Guid.NewGuid(), DateTime.Now));

var t2 = fsql.Select<T>()
  .Where(a => lst.Contains(a.Id, a.ct1))
  .ToList();

//SELECT .. FROM ..
//WHERE (a."Id" = '685ee1f6-bdf6-4719-a291-c709b8a1378f' AND a."ct1" = '2019-12-07 23:55:27' OR 
//a."Id" = '5ecd838a-06a0-4c81-be43-1e77633b7404' AND a."ct1" = '2019-12-07 23:55:27')
         */
    }
}

#region 多表连接例子  https://github.com/dotnetcore/FreeSql/wiki/%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2
/*
 //多表连接例子

fsql.Select<Topic, Category, CategoryType>()
  .LeftJoin((a,b,c) => a.CategoryId == b.Id)
  .LeftJoin((a,b,c) => b.ParentID == c.Id)
  .Where((a,b,c) => c.Id > 0)
  .ToList((a,b,c) => new { a,b,c });

//或者
fsql.Select<Topic>().From<Category, CategoryType>((s, b, c) => s
  .LeftJoin(a => a.CategoryId == b.Id)
  .LeftJoin(a => b.ParentID == c.Id))
  .Where((a,b,c) => c.Id > 0)
  .ToList((a,b,c) => new { a,b,c });
//SELECT ...
//FROM `Topic` a
//LEFT JOIN `Category` b ON a.`CategoryId` = b.`Id`
//LEFT JOIN `CategoryType` c ON b.`ParentId` = c.`Id`
//WHERE c. `Id` > 0
 */
#endregion

#region 调用事务的例子
/*
    try
    {
        DB.Transaction(() => {
            //DB.Ado.TransactionCurrentThread 获得当前事务对象。如果有异常 会抛异常 会自动回滚。
    
            //删除
            if (ids.Count > 0)
                DB.Delete<BoYuan.Entity.tb>(ids).ExecuteAffrows();
    
            //批量更新
            if (updateList.Count > 0)
            {
                DB.Update<BoYuan.Entity.tb>().SetSource(updateList).ExecuteAffrows();
            }
    
            //批量添加
            if (addList.Count > 0)
            {
                DB.Insert<BoYuan.Entity.tb>(addList).ExecuteAffrows(); //
            }
        });
    
        saveState = true;               
    }
    catch (Exception ex)
    {
        saveState = false;
    }
 */
#endregion

#region Dto 映射查询
/*
Dto 映射查询

fsql.Select<Song>().ToList<Dto>();
//默认的映射查询，Dto 与 Song 属性名相同的被查询

fsql.Select<Song>().ToList(a => new DTO { xxx = a.ext }) 
//情况1：附加所有映射，再额外映射 ext，返回 List<DTO>

fsql.Select<Song>().ToList(a => new Song { id = a.id }) 
//情况2：只查询 id，返回 List<Song>

fsql.Select<Song>().ToList(a => new { id = a.id }) 
//情况3：只查询 id，返回 List<匿名对象>

fsql.Select<Song>().ToList(a => new DTO(a.id))
//情况4：只查询 id，返回 List<DTO>

fsql.Select<Song>().ToList(a => new DTO(a.id) { xxx = a.ext })
//情况5：查询 id, ext，返回 List<DTO>

fsql.Select<Song>().ToList(a => new Song(a.id))
//情况6：查询 id，返回 List<Song>

fsql.Select<Song>().ToList(a => new Song(a.id) { xxx = a.ext })
//情况7：查询 id, ext，返回 List<Song>
GroupBy 所有方法不使用 DTO 映射规则
这种映射支持单表/多表，在查询数据之前映射（不是先查询所有字段再到内存映射）

查找规则，查找属性名，会循环内部对象 _tables（join 查询后会增长），以 主表优先查，直到查到相同的字段。

如：

A, B, C 都有 id，Dto { id, a1, a2, b1, b2 }，A.id 被映射。也可以指定 id = C.id 映射。


总结  默认都会按照字段名称匹配来映射，  就是一些特殊的字段，需要你自己来设定(附加映射)。

自动映射，可能导致一些映射不是想要的
比如Dto Name 在多表查询得时候，多个表都有 Name 字段，默认会映射到第一个表的 Name 查询
此时，如果像将它改成第二个表 Name，ToList((a,b) => new Dto {Name=b.Name})  
Dto 其他字段不指定，会按字段名称自动映射

还有一种情况，Dto 有一两个属性名，和字段不一样的时候，也可以这样附加映射
ToList(a=> new Dto {NameXxx=a.Name})
 */

#endregion